

/**
	AUTHOR		: VEERU
	DATE		: JULY 24th 2005
	DESCRIPTION	: THIS PROCEDURE IS USED TO LOAD ALL THE PRODUCTS INCLUDING UN-APPROVED.

*/


CREATE        PROC SP_ALL_PRODUCTS_LIST_BY_TYPE

AS
BEGIN
	DECLARE @RESULTS TABLE(PROCESSSTEP_ID INT, IST_MANUFACTURING_PART_NUMBER VARCHAR (50), IST_MARKETING_PART_NUMBER VARCHAR (50), PRODUCT_ID INT, P_TYPE VARCHAR (50), P_COMMENTS VARCHAR (200) )
	DECLARE @PROC_ID VARCHAR (20)
	DECLARE @MFT_PART_NO VARCHAR (50)
	DECLARE @MKT_PART_NO VARCHAR (50)
	DECLARE @PROD_ID INT
	DECLARE @P_TYPE VARCHAR (50)
	DECLARE @P_DESCRIPTION VARCHAR (200)
	DECLARE NCURSOR CURSOR FOR
	SELECT PROCESS_STEP.PROCESSSTEP_ID, PRODUCT.PART_NUMBER, PRODUCT.P_MARKETING_PART_NUMBER, PRODUCT.PRODUCT_ID, PRODUCT.P_TYPE, PRODUCT.P_COMMENTS FROM PRODUCT,PROCESS_STEP  WHERE PRODUCT.P_TYPE IS NULL AND PRODUCT.PROCESSSTEP_ID IN (SELECT PROCESSSTEP_ID FROM PROCESS_STEP WHERE PS_TYPE_NAME LIKE 'FINISHED GOODS') AND PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID ORDER BY PRODUCT.PART_NUMBER

	OPEN NCURSOR
	FETCH NEXT FROM NCURSOR INTO @PROC_ID,@MFT_PART_NO,@MKT_PART_NO,@PROD_ID,@P_TYPE, @P_DESCRIPTION 
	WHILE @@FETCH_STATUS = 0
		BEGIN
			INSERT INTO @RESULTS VALUES(@PROC_ID,@MFT_PART_NO,@MKT_PART_NO,@PROD_ID,@P_TYPE,@P_DESCRIPTION)
			SET @PROC_ID 	   = NULL
			SET @MFT_PART_NO   = NULL
			SET @MKT_PART_NO   = NULL
			SET @PROD_ID       = NULL
			SET @P_TYPE        = NULL
			SET @P_DESCRIPTION = NULL
			FETCH NEXT FROM NCURSOR INTO @PROC_ID,@MFT_PART_NO,@MKT_PART_NO,@PROD_ID,@P_TYPE, @P_DESCRIPTION 
		END
	CLOSE NCURSOR
	DEALLOCATE NCURSOR

	INSERT INTO @RESULTS VALUES(NULL,'---- BOARD -----','---- BOARD -----',NULL,NULL,NULL)

	DECLARE BCURSOR CURSOR FOR
	SELECT PROCESS_STEP.PROCESSSTEP_ID, PRODUCT.PART_NUMBER, PRODUCT.P_MARKETING_PART_NUMBER, PRODUCT.PRODUCT_ID, PRODUCT.P_TYPE,PRODUCT.P_COMMENTS FROM PRODUCT,PROCESS_STEP  WHERE PRODUCT.P_TYPE = 'BOARD' AND PRODUCT.PROCESSSTEP_ID IN (SELECT PROCESSSTEP_ID FROM PROCESS_STEP WHERE PS_TYPE_NAME LIKE 'FINISHED GOODS') AND PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID ORDER BY PRODUCT.PART_NUMBER

	OPEN BCURSOR
		FETCH NEXT FROM BCURSOR INTO @PROC_ID,@MFT_PART_NO,@MKT_PART_NO,@PROD_ID,@P_TYPE,@P_DESCRIPTION 
		WHILE @@FETCH_STATUS = 0
		BEGIN
			INSERT INTO @RESULTS VALUES(@PROC_ID,@MFT_PART_NO,@MKT_PART_NO,@PROD_ID,@P_TYPE,@P_DESCRIPTION)
			SET @PROC_ID       = NULL
			SET @MFT_PART_NO   = NULL
			SET @MKT_PART_NO   = NULL
			SET @PROD_ID 	   = NULL
			SET @P_TYPE        = NULL
			SET @P_DESCRIPTION = NULL
			FETCH NEXT FROM BCURSOR INTO @PROC_ID,@MFT_PART_NO,@MKT_PART_NO,@PROD_ID,@P_TYPE,@P_DESCRIPTION 
		END
	CLOSE BCURSOR
	DEALLOCATE BCURSOR

	INSERT INTO @RESULTS VALUES(NULL,'---- KIT -----','---- KIT -----',NULL,NULL,NULL)

	DECLARE KCURSOR CURSOR FOR
	SELECT PROCESS_STEP.PROCESSSTEP_ID, PRODUCT.PART_NUMBER, PRODUCT.P_MARKETING_PART_NUMBER, PRODUCT.PRODUCT_ID, PRODUCT.P_TYPE,PRODUCT.P_COMMENTS FROM PRODUCT,PROCESS_STEP  WHERE PRODUCT.P_TYPE = 'KIT' AND PRODUCT.PROCESSSTEP_ID IN (SELECT PROCESSSTEP_ID FROM PROCESS_STEP WHERE PS_TYPE_NAME LIKE 'FINISHED GOODS') AND PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID ORDER BY PRODUCT.PART_NUMBER

	OPEN KCURSOR
		FETCH NEXT FROM KCURSOR INTO @PROC_ID,@MFT_PART_NO,@MKT_PART_NO,@PROD_ID,@P_TYPE,@P_DESCRIPTION 
		WHILE @@FETCH_STATUS = 0
		BEGIN
			INSERT INTO @RESULTS VALUES(@PROC_ID,@MFT_PART_NO,@MKT_PART_NO,@PROD_ID,@P_TYPE,@P_DESCRIPTION )
			SET @PROC_ID       = NULL
			SET @MFT_PART_NO   = NULL
			SET @MKT_PART_NO   = NULL
			SET @PROD_ID       = NULL
			SET @P_TYPE        = NULL
			SET @P_DESCRIPTION = NULL
			FETCH NEXT FROM KCURSOR INTO @PROC_ID,@MFT_PART_NO,@MKT_PART_NO,@PROD_ID,@P_TYPE,@P_DESCRIPTION 

		END
	CLOSE KCURSOR
	DEALLOCATE KCURSOR

END

SELECT * FROM @RESULTS   

